Release 10.1A: OpenEdge Data Management:
SQL Development

The READPAST locking hint

The READPAST locking hint skips locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation level and will read only past row-level locks. This only applies to the SELECT statement.

The locking hint clause, such as READPAST, can only be specified in the main SELECT statement, but not in the subquery SELECT statement in the search condition of the WHERE clause.

The SELECT statement uses the following syntax:

Syntax

SELECT column_list FROM table_list [ WHERE search_condition ] [ GROUP BY grouping_condition ] [ HAVING search_condition ] [ ORDER BY ordering_condition ]     [ WITH locking_hints ]     [ FOR UPDATE update_condition ] ;

The WITH phrase uses the following syntax:

Syntax

WITH ( READPAST [ NOWAIT ][ WAIT timeout ] )

NOWAIT — Causes the SELECT statement to skip (read past) the row immediately if a lock cannot be acquired on a row in the selection set because of the lock held by some other transaction. The default behavior is for the transaction to wait until it gets the required lock or until it times out waiting for the lock.
WAIT timeout — Overrides the default lock-wait time-out. The time-out value is in seconds and can be given a 0 or any positive number.

The SELECT statements in Example 8–4 and Example 8–5 illustrate the use of the READPAST locking hint.

Example 8–4: READPAST locking hint with NOWAIT specified

SELECT * FROM Customer WHERE “CustNum” < 100 ORDER BY “CustNum” FOR UPDATE WITH (READPAST NOWAIT);

Example 8–5: READPAST locking hint with WAIT time-out specified

SELECT * FROM Customer WHERE “CustNum” < 100 ORDER BY “CustNum” FOR UPDATE WITH (READPAST WAIT 1);